﻿Imports BUS
Imports DTO
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO
Public Class ImportHocSinhExcel

    Dim _dtt As New DataTable()

    Private Sub btnimport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnimport.Click
        Dim _dataHS As New DataTable()
        Dim _bushs As New CHocSinhBus()
        _dataHS = _bushs.LayDSHocSinhAdapter()
        Dim i, j As Integer

        For i = 0 To _dtt.Rows.Count - 1
            If i > _dataHS.Rows.Count - 1 Then
                _dataHS.Rows.Add(_dtt(i)(0), _dtt(i)(1), _dtt(i)(2), _dtt(i)(3), _dtt(i)(4), _dtt(i)(5))
            Else
                For j = 0 To _dtt.Columns.Count - 1
                    _dataHS.Rows(i)(j) = _dtt.Rows(i)(j)
                Next
            End If
        Next

        If _bushs.CapNhatHSAdapter(_dataHS) > 0 Then
            MessageBox.Show("OK")
        Else
            MessageBox.Show("false")
        End If

    End Sub

    Public Sub ExcelToDatabase()
        Try
            Dim _app As New Excel.Application()
            Dim _workbook As Excel.Workbook
            Dim _worksheet As Excel.Worksheet
            Dim _range As Excel.Range
            Dim _dlg As New OpenFileDialog()
            _dlg.Filter = "|*.xls||*.xlsx"
            _dtt.Columns.Add("MaHocSinh", GetType(Int32))
            _dtt.Columns.Add("HoTen", GetType(String))
            _dtt.Columns.Add("NgaySinh", GetType(DateTime))
            _dtt.Columns.Add("Email", GetType(String))
            _dtt.Columns.Add("GioiTinh", GetType(String))
            _dtt.Columns.Add("DiaChi", GetType(String))
            Dim _row As DataRow
            If _dlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
                _workbook = _app.Workbooks.Open(_dlg.FileName, 0, True, 5, "", "", True, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", False, False, 0, True, 1, 0)
                _worksheet = _workbook.Sheets(1)
                _range = _worksheet.UsedRange()
                Dim i As Integer
                Dim _rag As Excel.Range
                For i = 2 To _range.Rows.Count
                    _row = _dtt.NewRow()
                    _rag = _range.Cells(i, 1)
                    _row(0) = Int32.Parse(_rag.Text)
                    _rag = _range.Cells(i, 2)
                    _row(1) = _rag.Text
                    _rag = _range.Cells(i, 3)
                    _row(2) = DateTime.Parse(_rag.Text)
                    _rag = _range.Cells(i, 4)
                    _row(3) = _rag.Text
                    _rag = _range.Cells(i, 5)
                    _row(4) = _rag.Text
                    _rag = _range.Cells(i, 6)
                    _row(5) = _rag.Text
                    _dtt.Rows.Add(_row)
                Next
            End If
        Catch ex As Exception
            MessageBox.Show("Loi --> " + ex.ToString())
        End Try

    End Sub

    Private Sub ImportHocSinhExcel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ExcelToDatabase()
        dtgvexcel.DataSource = _dtt
    End Sub

    Private Sub btnthoat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnthoat.Click
        Me.Close()
    End Sub
End Class